package com.training.admin.service.impl;

import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.FilenameUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.demo2do.core.entity.exception.BusinessException;
import com.demo2do.core.entity.page.Page;
import com.demo2do.core.persistence.GenericDaoSupport;
import com.demo2do.core.persistence.annotation.Dao;
import com.demo2do.core.persistence.support.QueryFilter;
import com.demo2do.core.persistence.support.QueryOrder;
import com.demo2do.core.utils.CollectionUtils;
import com.demo2do.core.utils.JsonUtils;
import com.training.admin.dao.IdmUserDao;
import com.training.admin.dao.ProjectTraineeDao;
import com.training.admin.dao.TraineeDao;
import com.training.admin.dao.TrainingResultDao;
import com.training.admin.entity.project.Project;
import com.training.admin.entity.project.ProjectPhase;
import com.training.admin.entity.project.ProjectTrainee;
import com.training.admin.entity.project.TrainingResult;
import com.training.admin.entity.trainee.IdmUser;
import com.training.admin.entity.trainee.Trainee;
import com.training.admin.handler.FileHandler;
import com.training.admin.handler.support.FileStorage;
import com.training.admin.service.ProjectTraineeService;

/**
 * 
 * @author Rockett
 *
 */
/**
 * @author xiaoHU
 *
 */
/**
 * @author xiaoHU
 *
 */
@Service("projectTraineeService")
public class ProjectTraineeServiceImpl extends DataServiceImpl<ProjectTrainee> implements ProjectTraineeService {
    private static final Logger logger = LoggerFactory.getLogger(ProjectTraineeServiceImpl.class);

    @Autowired
    private GenericDaoSupport genericDaoSupport;

    @Dao
    private ProjectTraineeDao projectTraineeDao;
   
    @Dao
    private TraineeDao traineeDao;
    
    @Autowired
    private FileHandler fileHandler;
    
    @Dao
    private IdmUserDao idmUserDao;
    
    @Dao
    private TrainingResultDao trainingResultDao;
    
    @Value("#{config['file.excel.error.path']}")
    private String uploadPath;
    
    @Value("#{config['cloud.error.host']}")
    private String httpPath;

    /**
     * @param login
     * @param file
     * @return
     */
    private Map<String, Object> parse(MultipartFile file) {

        // 0. prepare data
        Map<String, Object> result = new HashMap<String, Object>();
        List<Trainee> trainees = new ArrayList<Trainee>();
       
        DataFormatter formatter = new DataFormatter();
        Boolean fileError = false;

        try {

        	  logger.trace("#parse() - Starting parsing upload file.");

              // 1. get workbook
              Workbook workbook = WorkbookFactory.create(file.getInputStream());
             
              // 2. get sheet and rows
              Sheet sheet = workbook.getSheetAt(0);
              Iterator<Row> rows = sheet.iterator();
              

              // 3. construct trainees
              while (rows.hasNext()) {

                  Row row = rows.next();
                  int rowNum = row.getRowNum();
                  logger.info("# rowNum is [{}]", rowNum);

                  if (rowNum < 2) {
                      continue;
                  }
                  
                  String name = formatter.formatCellValue(row.getCell(0)); // 姓名
                  logger.info("# name is [{}]", name);
                  
                  String email = formatter.formatCellValue(row.getCell(1)); // 姓名
                  logger.info("# email is [{}]", email);
                  if (StringUtils.isBlank(email)) { // 判断Excel为空行的情况
                      continue;
                  }
                  
                  //Trainee trainee = new Trainee(code, name, alias, departmentFullName, departmentName, mobile, email);
                  IdmUser idmUser = idmUserDao.loadByEmail(email);
                  if(idmUser == null) {
                    logger.info("# no user in idm with mail[{}]", email);
                  	fileError = true;
                  	row.createCell(2).setCellValue("该用户不存在");
                  } else {
                  	Trainee trainee = new Trainee(idmUser);
                  	trainees.add(trainee);
                  }
                  
              }
              if(fileError == true) {
            	  String fileName = new Date().getTime() + "-project-trainee.error.xls";
              	   FileOutputStream op = new FileOutputStream(uploadPath + fileName);
                     workbook.write(op);
                     op.flush();
                     op.close();
                   result.put("filePath", httpPath + "/" +fileName);
              }
           
              
              logger.trace("#parse() - Finishing parsing file with {} records.", trainees.size());

        } catch (Exception e) {
            logger.error("#parse() - Finishing parsing file with errors.", e);
        }

        result.put("trainees", trainees);
        
        return result;

    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * com.training.admin.service.ProjectTraineeService#listByProjectId(java.lang.
     * Long)
     */
    public List<ProjectTrainee> listByProjectId(Long projectId) {
        return projectTraineeDao.listByProjectId(projectId);
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * com.training.admin.service.ProjectTraineeService#countByProjectId(java.lang.
     * Long)
     */
    public int countByProjectId(Long projectId) {
        // if projectId is null, return 0;
        if (null == projectId)
            return 0;

        QueryFilter queryFilter = new QueryFilter().addSentence("project.id =:projectId", "projectId", projectId);
        return super.count(ProjectTrainee.class, queryFilter);

    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * com.training.admin.service.ProjectTraineeService#listByProjectId(java.lang.
     * Long, com.demo2do.core.entity.page.Page)
     */
    public List<ProjectTrainee> listByProjectId(Long projectId, Page page) {

        QueryFilter queryFilter = new QueryFilter().addSentence("project.id =:projectId", "projectId", projectId);
        QueryOrder queryOrder = new QueryOrder().add("trainee.name", "ASC");
        page.calculate();
        return super.listByPage(ProjectTrainee.class, queryFilter, queryOrder, page);
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * com.training.admin.service.ProjectTraineeService#countByProjectAndTraineeId(
     * java.lang.Long, java.lang.Long)
     */
    public int countByProjectAndTraineeId(Long projectId, Long traineeId) {
        QueryFilter queryFilter = new QueryFilter().addSentence("project.id =:projectId", "projectId", projectId);
        queryFilter = queryFilter.addSentence("trainee.id =:traineeId", "traineeId", traineeId);
        return super.count(ProjectTrainee.class, queryFilter);
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.training.admin.service.ProjectTraineeService#
     * countCompletedTraineeByProjectId(java.lang.Long)
     */
    @Override
    public int countCompletedTraineeByProjectId(Long projectId) {
        // if projectId is null, return 0;
        if (null == projectId)
            return 0;

        QueryFilter queryFilter = new QueryFilter().addSentence(" project.id =:projectId and complete=true ",
                "projectId", projectId);
        return super.count(ProjectTrainee.class, queryFilter);
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.training.admin.service.ProjectTraineeService#createByFile(org.
     * springframework. web.multipart.MultipartFile)
     */
    @SuppressWarnings("unchecked")
    @Override
    public Map<String, Object> createByFile(MultipartFile file, Project project) {
    	
        try {

            // 1. save file
            FileStorage fileStorage = new FileStorage("project-trainee/upload", "project-trainee-upload",
                    FilenameUtils.getExtension(file.getOriginalFilename()));
            fileHandler.save(fileStorage, file.getInputStream());

            // 2. parse file to
            Map<String, Object> result = this.parse(file);

            // 3. save
            List<Trainee> trainees = (List<Trainee>) result.get("trainees");
            
            ProjectTrainee projectTraineeList = null;

            for (Trainee trainee : trainees) {

                Trainee exitTrainee = traineeDao.loadByEmail(trainee.getEmail());

                if (exitTrainee == null) {
                    genericDaoSupport.save(trainee); // 学员不在库中，先save新学员
                } else {
                    trainee = exitTrainee;
                    projectTraineeList = projectTraineeDao.load(project.getId(), trainee.getId()); // 学员已存在库中，查是否在当前项目中
                }

                if (projectTraineeList == null) { // 当前学员不在项目中，save学员进入当前项目
                    ProjectTrainee projectTrainee = new ProjectTrainee(project, trainee);
                    genericDaoSupport.save(projectTrainee);
                }
            }

            logger.trace("#upload() - batch upload success");
            return result;

        } catch (Exception e) {
            logger.error("#upload() - batch upload driver fail, exception is [{}]", e);
            throw BusinessException.newInstance("500", "批量上传失败，" + e.getMessage());
        }
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * com.training.admin.service.ProjectTraineeService#create(com.training.admin.
     * entity.project.Project, java.util.List)
     */
    public void create(Project project, List<Long> traineeIds) {

        List<IdmUser> idmUsers = idmUserDao.listByIds(traineeIds);
        List<Trainee> trainees = genericDaoSupport.loadAll(Trainee.class);
        Map<String, Trainee> traineeMap = CollectionUtils.constructMapValue(trainees, "email");
        
        List<ProjectTrainee> itemProjectTrainees = projectTraineeDao.listByProject(project);
        Map<String, ProjectTrainee> itemMap = CollectionUtils.constructMapValue(itemProjectTrainees, "trainee.email");

        for (IdmUser idmUser : idmUsers) {
            
            Trainee exitTrainee = traineeMap.get(idmUser.getMail());
            if (exitTrainee != null) {
                genericDaoSupport.update(exitTrainee.updateByIdmUser(idmUser));
                
                ProjectTrainee exitProjectTrainee = itemMap.get(exitTrainee.getEmail());
                if (exitProjectTrainee == null) { // 当前学员不在项目中，save学员进入当前项目
                    ProjectTrainee projectTrainee = new ProjectTrainee(project, exitTrainee);
                    genericDaoSupport.save(projectTrainee);
                }
                
            }else {
                Trainee newTrainee = new Trainee(idmUser);
                genericDaoSupport.save(newTrainee);
                
                ProjectTrainee exitProjectTrainee = itemMap.get(newTrainee.getEmail());
                if (exitProjectTrainee == null) { // 当前学员不在项目中，save学员进入当前项目
                    ProjectTrainee projectTrainee = new ProjectTrainee(project, newTrainee);
                    genericDaoSupport.save(projectTrainee);
            }
        }
    }
  }

    /*
     * (non-Javadoc)
     * 
     * @see
     * com.training.admin.service.ProjectTraineeService#listTrainee(java.lang.Long)
     */
    public List<Trainee> listTrainee(Long projectId) {
        List<ProjectTrainee> projectTraineeList = projectTraineeDao.listByProjectId(projectId);

        List<Trainee> projectTrainees = new ArrayList<Trainee>();

        for (int i = 0; i < projectTraineeList.size(); i++) {

            projectTrainees.add(projectTraineeList.get(i).getTrainee());

        }

        return projectTrainees;
    }

    /* (non-Javadoc)
     * @see com.training.admin.service.ProjectTraineeService#updateProjectTraineeByFile(org.springframework.web.multipart.MultipartFile, com.training.admin.entity.project.Project, java.lang.Long)
     */
    @SuppressWarnings("unchecked")
    public void updateProjectTraineeByFile(MultipartFile file, Project project, Long projectPhaseId) {
        try {

            FileStorage fileStorage = new FileStorage("project-result/upload", "project-result-score-upload", FilenameUtils.getExtension(file.getOriginalFilename()));
            fileHandler.save(fileStorage, file.getInputStream());
            
            List<ProjectTrainee> projectTrainees = this.parseProjectTrainee(file);
            logger.error("projectTrainees is [{}]", JsonUtils.toJson(projectTrainees));
            
            ProjectPhase projectPhase = genericDaoSupport.load(ProjectPhase.class, projectPhaseId);
            List<TrainingResult> trainingResults = trainingResultDao.listByProjectPhase(projectPhase);
            Map<String, TrainingResult> trainingResultMap = CollectionUtils.constructMapValue(trainingResults, "trainee.email");

            List<String> emails = CollectionUtils.extractPropertyList(projectTrainees, "trainee.email");
            logger.error("trainee emails is [{}]", emails);
            List<Trainee> trainees = traineeDao.listByEmails(emails);
            Map<String, Trainee> traineesMap = CollectionUtils.constructMapValue(trainees, "email");
            
            List<ProjectTrainee> exitProjectTrainees = projectTraineeDao.listByTraineeAndProject(emails, project);
            Map<String, ProjectTrainee> exitProjectTraineesMap = CollectionUtils.constructMapValue(exitProjectTrainees, "trainee.email");
            
            for (ProjectTrainee projectTrainee : projectTrainees) {
                
                ProjectTrainee exitProjectTrainee = exitProjectTraineesMap.get(projectTrainee.getTrainee().getEmail());
                Trainee trainee = traineesMap.get(projectTrainee.getTrainee().getEmail());
                
                if(trainee == null) {
                    logger.error("trainee [{}] is not exit", projectTrainee.getTrainee().getEmail());
                    continue;
                }
                
                if(exitProjectTrainee == null) {
                    logger.error("trainee [{}] is not exit in current project[{}]", projectTrainee.getTrainee().getEmail(), project.getId());
                    continue;
                }
                
                exitProjectTrainee.uploadProjectTrainee(projectTrainee.getComplete(),
                                                        projectTrainee.getResit(),
                                                        projectTrainee.getScore(),
                                                        projectTrainee.getAbsence(),
                                                        projectTrainee.getComment(),
                                                        projectTrainee.getSpotScore(),
                                                        projectTrainee.getInterviewTimes(),
                                                        projectTrainee.getInterview(),
                                                        projectTrainee.getCounselorTimes(),
                                                        projectTrainee.getCounselor(),
                                                        projectTrainee.getSeed());
                genericDaoSupport.update(exitProjectTrainee);
                
                TrainingResult exitTrainingResult = trainingResultMap.get(projectTrainee.getTrainee().getEmail());
                if (exitTrainingResult != null) {
                    genericDaoSupport.update(exitTrainingResult.updateByProjectTrain(projectTrainee));
                }
                
                TrainingResult newTrainingResult = new TrainingResult(projectTrainee, projectPhase);
                genericDaoSupport.save(newTrainingResult);
            }
            
        } catch (Exception e) {
            logger.error("#upload() - batch upload driver fail, exception is [{}]", e);
            throw BusinessException.newInstance("500", "批量上传失败，" + e.getMessage());
        }
    }
    
    /*
     * (non-Javadoc)
     * @see com.training.admin.service.ProjectTraineeService#editProjectTrainee(com.training.admin.entity.project.ProjectTrainee, com.training.admin.entity.trainee.Trainee, java.lang.Long)
     */
    public void editProjectTrainee(ProjectTrainee projectTrainee, Trainee trainee, Long projectPhaseId) {
        
        if(trainee == null) {
            logger.error("trainee [{}] is not exit", projectTrainee.getTrainee().getEmail());
            return;
        }
        
        
        ProjectPhase projectPhase = genericDaoSupport.load(ProjectPhase.class, projectPhaseId);
        Project project = projectPhase.getProject();
        ProjectTrainee exitProjectTrainee = projectTraineeDao.loadByTraineeAndProject(trainee, project);
        if(exitProjectTrainee == null) {
            logger.error("trainee [{}] is not exit in current project[{}]", projectTrainee.getTrainee().getEmail(), project.getId());
            return;
        }
        
        if(project.getSecondCategory().getId() == 15 || project.getSecondCategory().getId() == 16) {
            exitProjectTrainee.uploadProjectTrainee(projectTrainee.getComplete(),
                    projectTrainee.getResit(),
                    projectTrainee.getScore(),
                    projectTrainee.getAbsence(),
                    projectTrainee.getComment(),
                    projectTrainee.getSpotScore(),
                    projectTrainee.getInterviewTimes(),
                    projectTrainee.getInterview(),
                    projectTrainee.getCounselorTimes(),
                    projectTrainee.getCounselor(),
                    projectTrainee.getSeed());
        } else {
            exitProjectTrainee.uploadProjectTrainee(projectTrainee.getComplete(),
                    projectTrainee.getResit(),
                    projectTrainee.getScore(),
                    projectTrainee.getAbsence(),
                    projectTrainee.getComment());
        }
        genericDaoSupport.update(exitProjectTrainee);
        
        TrainingResult exitTrainingResult = trainingResultDao.load(projectPhase, trainee);
        if (exitTrainingResult != null) {
            genericDaoSupport.update(exitTrainingResult.updateByProjectTrain(projectTrainee, project.getSecondCategory().getId()));
        }
        
        TrainingResult newTrainingResult = new TrainingResult(projectTrainee, trainee, projectPhase, project.getSecondCategory().getId());
        genericDaoSupport.save(newTrainingResult);
        
    }

    /**
     * @param login
     * @param file
     * @return
     */
    private  List<ProjectTrainee> parseProjectTrainee(MultipartFile file) {

        // 0. prepare data
        List<ProjectTrainee> projectTrainees = new ArrayList<ProjectTrainee>();
        DataFormatter formatter = new DataFormatter();

        try {

            logger.trace("#parse() - Starting parsing upload file.");

            // 1. get workbook
            Workbook workbook = WorkbookFactory.create(file.getInputStream());

            // 2. get sheet and rows
            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rows = sheet.iterator();

            // 3. construct trainees
            while (rows.hasNext()) {

                Row row = rows.next();
                int rowNum = row.getRowNum();

                if (rowNum < 3) {
                    continue;
                }

                String email = formatter.formatCellValue(row.getCell(1)); // 学员邮箱
                if (StringUtils.isBlank(email)) { // 判断Excel为空行的情况
                    continue;
                }
                String complete = formatter.formatCellValue(row.getCell(2)); // 判断是否结业
                if (StringUtils.isBlank(complete)) { // 判断Excel为空行的情况
                    continue;
                }
                String resit = formatter.formatCellValue(row.getCell(3)); // 判断是否补考
                if (StringUtils.isBlank(resit)) { // 判断Excel为空行的情况
                    continue;
                }
                String score = formatter.formatCellValue(row.getCell(4)); // 最终成绩
                if (StringUtils.isBlank(score)) { // 判断Excel为空行的情况
                    continue;
                }
                String absence = formatter.formatCellValue(row.getCell(5)); // 缺勤天数
                if (StringUtils.isBlank(absence)) { // 判断Excel为空行的情况
                    continue;
                }
                String comment = formatter.formatCellValue(row.getCell(6)); // 备注
                String spotScore = formatter.formatCellValue(row.getCell(7)); // 现场笔试成绩
                String interviewTimes = formatter.formatCellValue(row.getCell(8)); // 面试官认证次数
                String interview = formatter.formatCellValue(row.getCell(9)); // 面试官认
                String counselorTimes = formatter.formatCellValue(row.getCell(10)); // 辅导师认证次数
                String counselor = formatter.formatCellValue(row.getCell(11)); // 副导师认证
                String seed = formatter.formatCellValue(row.getCell(12)); // 是否种子
                
                Trainee trainee = traineeDao.loadByEmail(email);
                
                ProjectTrainee projectTrainee = new ProjectTrainee(trainee, complete,
                                                                   resit, score, absence,
                                                                   comment, spotScore,
                                                                   interviewTimes, interview,
                                                                   counselorTimes, counselor,
                                                                   seed);
                projectTrainees.add(projectTrainee);
            }

            logger.trace("#parse() - Finishing parsing file with {} records.", projectTrainees.size());
            
        } catch (Exception e) {
            logger.error("#parse() - Finishing parsing file with errors.", e);
        }

        return projectTrainees;

    }

    /* (non-Javadoc)
     * @see com.training.admin.service.ProjectTraineeService#countByProject(com.training.admin.entity.project.Project)
     */
    public int countByProject(Project project) {
        return projectTraineeDao.countByProject(project);
    }

    /* (non-Javadoc)
     * @see com.training.admin.service.ProjectTraineeService#listByProject(com.training.admin.entity.project.Project, com.demo2do.core.entity.page.Page)
     */
    public List<ProjectTrainee> listByProject(Project project, Page page) {
        List<ProjectTrainee> projectTrainees = projectTraineeDao.listProjectTrainee(project, page.getBeginIndex(), page.getMaxResultRecords());
        page.calculate();
        return projectTrainees;
    }
    
    /*
     * (non-Javadoc)
     * @see com.training.admin.service.ProjectTraineeService#load(com.training.admin.entity.project.Project, com.training.admin.entity.trainee.Trainee)
     */
    public ProjectTrainee load(Project project, Trainee trainee) {
        return projectTraineeDao.load(project.getId(), trainee.getId());
    }

}
